﻿using sqlsearcher.Models;
using sqlsearcher.Models.Services;
using sqlsearcher.Services.IService;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace sqlsearcher.Services.DB
{
    /// <summary>
    /// MySqlSearch
    /// 创建日期 2023/1/17 16:48:19
    /// Author: 名字什么都是浮云
    /// </summary>	
    public class MySqlSearch : ISearch
    {
        public Tuple<int, DataTable> GetCodeDetails(ISqlDbUtils dbUtils, string db, string value)
        {
            var sql = $@"SELECT 
            column_name cName,
            column_comment cDesc,
            CASE WHEN extra='auto_increment' THEN '自增'ELSE '' END cTag,
            CASE WHEN column_key='PRI' THEN '是' ELSE '否' END cKey,
            column_type cType,
            CASE WHEN is_nullable='YES' THEN '可空' ELSE '' END cNul,
            column_default cDef
            FROM information_schema.columns WHERE table_schema ='{db}' AND table_name = '{value}'; ";
            var dt = dbUtils.Query(db,sql);
            var isHas = dt != null && dt.Rows.Count > 0;
            return Tuple.Create(isHas ? dt.Rows.Count : 0, dt);
        }

        public Tuple<bool, List<TreeMapInfo>> GetColumns(ISqlDbUtils dbUtils, string db, string value)
        {
            var sql = $@"SELECT 
            column_name Node,
            '{value}' ParentNode,
            CASE WHEN column_key='PRI' THEN '1' ELSE '0' END IsKey
            FROM information_schema.columns WHERE table_schema ='{db}' AND table_name = '{value}'; ";

            DataTable dt = dbUtils.Query(db,sql);

            var maps = dt.ToList<TreeMapInfo>();

            return Tuple.Create(maps != null && maps.Count > 0, maps);
        }

        public Tuple<bool, List<TreeMapInfo>> GetDataBases(ISqlDbUtils dbUtils)
        {
            var sql = "SHOW DATABASES";
            DataTable dt = new MySqlDbUtils().Query(sql);
            dt.Columns.Add("ParentNode");
            dt.Columns.Add("IsKey");
            dt.Columns["Database"].ColumnName = "Node";
            var maps = dt.ToList<TreeMapInfo>();

            return Tuple.Create(maps != null && maps.Count > 0, maps);
        }

        public Tuple<int, DataTable> GetDescs(ISqlDbUtils dbUtils, string db, string tb)
        {
            var sql = $@"SELECT 
            column_name 'DcName',
            column_comment 'Desc',
            column_comment 'Desc2',
            column_type cType
            FROM information_schema.columns WHERE table_schema ='{db}' AND table_name = '{tb}'; ";
            var dt = dbUtils.Query(db,sql);
            var isHas = dt != null && dt.Rows.Count > 0;
            return Tuple.Create(isHas ? dt.Rows.Count : 0, dt);
        }

        public Tuple<int, DataTable> GetDetails(ISqlDbUtils dbUtils, string db, string value)
        {
            var sql = $@"SELECT 
            column_name cName,
            column_comment cDesc,
            CASE WHEN extra='auto_increment' THEN '自增'ELSE '' END cTag,
            CASE WHEN column_key='PRI' THEN '是' ELSE '否' END cKey,
            column_type cType,
            CASE WHEN is_nullable='YES' THEN '可空' ELSE '' END cNul,
            column_default cDef
            FROM information_schema.columns WHERE table_schema ='{db}' AND table_name = '{value}'; ";
            var dt = dbUtils.Query(db,sql);
            var isHas = dt != null && dt.Rows.Count > 0;
            return Tuple.Create(isHas ? dt.Rows.Count : 0, dt);
        }

        public string GetTableDesc(ISqlDbUtils dbUtils, string tbName, string dbName)
        {
            var sql = $@"SELECT column_name 'name',column_comment 'value'
            FROM information_schema.columns WHERE table_schema ='{dbName}' AND table_name = '{tbName}'; ";

            DataTable dt = dbUtils.Query(dbName,sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0]["value"].TryString();
            }
            return tbName;
        }

        public Tuple<bool, List<TreeMapInfo>> GetTables(ISqlDbUtils dbUtils,string db)
        {
            string sql = $@"
            SELECT Table_NAME Node,'{db}' ParentNode,'0' IsKey
            FROM information_schema.tables
            WHERE TABLE_SCHEMA = '{db}'
            ORDER BY table_name asc; ";

            DataTable dt = dbUtils.Query(db,string.Format(sql, db));

            var maps = dt.ToList<TreeMapInfo>();

            return Tuple.Create(maps != null && maps.Count > 0, maps);
        }

        public DataTable GetTablesByDB(ISqlDbUtils dbUtils, string db)
        {
            var sql = $@"SELECT table_name '表名',create_time '创建日期',table_comment '描述'
            FROM information_schema.tables WHERE TABLE_SCHEMA = '{db}' ORDER BY create_time DESC";
            return dbUtils.Query(db,sql);
        }

        public string GetTableDescSQL()
        {
            var sql = $@"SELECT information_schema.columns .table_name TbName,information_schema.tables.table_comment TbDesc ,column_name CName,column_comment CDesc
              FROM information_schema.columns 
            LEFT JOIN information_schema.tables ON information_schema.tables.table_name=information_schema.columns.table_name";
            return sql;
        }

        public DataTable GetDs(ISqlDbUtils dbUtils, string sql, string db)
        {
            return dbUtils.Query(db,sql);
        }

        public DataSet GetDataSets(ISqlDbUtils dbUtils, string sql, string db)
        {
            return dbUtils.QueryDs(db,sql);
        }
    }
}
